package edu.ucsd.cse110;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.content.res.Resources;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import edu.ucsd.cse110.model.Lift;
import edu.ucsd.cse110.model.Log;
import edu.ucsd.cse110.model.Wod;

/**
 * TODO: Need documentation.
 */
public class LocalDatabaseManager extends SQLiteOpenHelper {
	private static final String DATABASE_NAME = "DroidFit.db";
	private static final int DATABASE_VERSION = 1;
	private Context mContext;
	private SQLiteDatabase mDatabase;

	/**
	 * Creates a helper object to create, open, and/or manage a database.
	 * 
	 * @param context
	 *            To use to open or create the database.
	 */
	public LocalDatabaseManager(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		mContext = context;
		mDatabase = getWritableDatabase();
	}

	/**
	 * Creates a helper object to create, open, and/or manage a database.
	 * 
	 * @param context
	 *            To use to open or create the database.
	 * @param name
	 *            Of the database file, or null for an in-memory database.
	 * @param factory
	 *            To use for creating cursor objects, or null for the default.
	 * @param version
	 *            Number of the database (starting at 1).
	 */
	public LocalDatabaseManager(Context context, String name,
			CursorFactory factory, int version) {
		super(context, name, factory, version);
		mContext = context;
		mDatabase = getWritableDatabase();
	}

	/**
	 * Closes the database.
	 */
	@Override
	public void close() {
		mDatabase.close();
	}

	/**
	 * Deletes lift entries from the database.
	 * 
	 * @param whereClause
	 *            the optional WHERE clause to apply when deleting. Passing null
	 *            will delete all rows.
	 * @return The number of rows affected if a whereClause is passed in, 0
	 *         otherwise. To remove all rows and get a count pass "1" as the
	 *         whereClause.
	 */
	public int deleteLift(String whereClause, String[] whereArgs) {
		return mDatabase.delete(Lift.class.getSimpleName(), whereClause,
				whereArgs);
	}

	/**
	 * Deletes lift entries by their id.
	 * 
	 * @param id
	 *            The id of the lift entries to be deleted.
	 * @return The number of rows affected if a whereClause is passed in, 0
	 *         otherwise. To remove all rows and get a count pass "1" as the
	 *         whereClause.
	 */
	public int deleteLiftById(int id) {
		return deleteLift(Lift.COLUMNS[0],
				new String[] { Integer.toString(id) });
	}

	/**
	 * Deletes log entries from the database.
	 * 
	 * @param whereClause
	 *            the optional WHERE clause to apply when deleting. Passing null
	 *            will delete all rows.
	 * @return The number of rows affected if a whereClause is passed in, 0
	 *         otherwise. To remove all rows and get a count pass "1" as the
	 *         whereClause.
	 */
	public int deleteLog(String whereClause, String[] whereArgs) {
		return mDatabase.delete(Log.class.getSimpleName(), whereClause,
				whereArgs);
	}

	/**
	 * Deletes log entries by their id.
	 * 
	 * @param id
	 *            The id of the log entries to be deleted.
	 * @return The number of rows affected if a whereClause is passed in, 0
	 *         otherwise. To remove all rows and get a count pass "1" as the
	 *         whereClause.
	 */
	public int deleteLogById(int id) {
		return deleteLog(String.format("%s = ?", Log.COLUMNS[0]),
				new String[] { Integer.toString(id) });
	}

	/**
	 * Deletes log entries by their WOD id.
	 * 
	 * @param id
	 *            The WOD id of the log entries to be deleted.
	 * @return The number of rows affected if a whereClause is passed in, 0
	 *         otherwise. To remove all rows and get a count pass "1" as the
	 *         whereClause.
	 */
	public int deleteLogByWodId(int id) {
		return deleteLog(String.format("%s = ?", Log.COLUMNS[2]),
				new String[] { Integer.toString(id) });
	}

	/**
	 * Deletes WOD entries from the database.
	 * 
	 * @param whereClause
	 *            the optional WHERE clause to apply when deleting. Passing null
	 *            will delete all rows.
	 * @return The number of rows affected if a whereClause is passed in, 0
	 *         otherwise. To remove all rows and get a count pass "1" as the
	 *         whereClause.
	 */
	public int deleteWod(String whereClause, String[] whereArgs) {
		return mDatabase.delete(Wod.class.getSimpleName(), whereClause,
				whereArgs);
	}

	/**
	 * Deletes WOD entries by their id.
	 * 
	 * @param id
	 *            The id of the WOD entries to be deleted.
	 * @return The number of rows affected if a whereClause is passed in, 0
	 *         otherwise. To remove all rows and get a count pass "1" as the
	 *         whereClause.
	 */
	public int deleteWodById(int id) {
		return deleteWod(String.format("%s = ?", Wod.COLUMNS[0]),
				new String[] { Integer.toString(id) });
	}

	/**
	 * @return A list of all log entries.
	 */
	public List<Lift> getAllLift() {
		return getLift(null, null, null, null, null);
	}

	/**
	 * @return A list of all log entries.
	 */
	public List<Log> getAllLog() {
		return getLog(null, null, null, null, null);
	}

	/**
	 * Gets the list of from the database.
	 * 
	 * @param selection
	 *            A filter declaring which rows to return, formatted as an SQL
	 *            WHERE clause (excluding the WHERE itself). Passing null will
	 *            return all rows for the given table.
	 * @param args
	 *            You may include ?s in selection, which will be replaced by the
	 *            values from selectionArgs, in order that they appear in the
	 *            selection. The values will be bound as Strings.
	 * @param groupBy
	 *            A filter declaring how to group rows, formatted as an SQL
	 *            GROUP BY clause (excluding the GROUP BY itself). Passing null
	 *            will cause the rows to not be grouped.
	 * @param having
	 *            A filter declare which row groups to include in the cursor, if
	 *            row grouping is being used, formatted as an SQL HAVING clause
	 *            (excluding the HAVING itself). Passing null will cause all row
	 *            groups to be included, and is required when row grouping is
	 *            not being used.
	 * @param orderBy
	 *            How to order the rows, formatted as an SQL ORDER BY clause
	 *            (excluding the ORDER BY itself). Passing null will use the
	 *            default sort order, which may be unordered.
	 * @return A list of lifts.
	 */
	public List<Lift> getLift(String selection, String[] args, String groupBy,
			String having, String orderBy) {
		String[] columns = Lift.COLUMNS;
		Cursor c = mDatabase.query(Lift.class.getSimpleName(), columns,
				selection, args, groupBy, having, orderBy);

		// Returns null if cursor is null.
		if (c == null) {
			return null;
		}

		List<Lift> results = new ArrayList<Lift>();

		while (c.moveToNext()) {
			Lift item = new Lift();

			item.setId(c.getInt(0));
			item.setLiftId(c.getInt(1));
			item.setDateOne(new Date(c.getLong(2)));
			item.setDateThree(new Date(c.getLong(3)));
			item.setDateFive(new Date(c.getLong(4)));
			item.setRepOne(c.getInt(5));
			item.setRepThree(c.getInt(6));
			item.setRepFive(c.getInt(7));

			results.add(item);
		}

		return results;
	}

	/**
	 * Gets a list of the lifts by their id.
	 * 
	 * @param id
	 *            The id of the lifts to be retrieved.
	 * @return A list of the lifts with the given id.
	 */
	public List<Lift> getLiftById(int id) {
		return getLift(String.format("%s = ?", Lift.COLUMNS[0]),
				new String[] { Integer.toString(id) }, null, null, null);
	}

	/**
	 * Gets a list of the lifts by their lift id.
	 * 
	 * @param id
	 *            The lift id of the lifts to be retrieved.
	 * @return A list of the lifts with the given lift id.
	 */
	public List<Lift> getLiftByLiftId(int id) {
		return getLift(String.format("%s = ?", Lift.COLUMNS[1]),
				new String[] { Integer.toString(id) }, null, null, null);
	}

	/**
	 * Gets the list of logs from the database.
	 * 
	 * @param selection
	 *            A filter declaring which rows to return, formatted as an SQL
	 *            WHERE clause (excluding the WHERE itself). Passing null will
	 *            return all rows for the given table.
	 * @param args
	 *            You may include ?s in selection, which will be replaced by the
	 *            values from selectionArgs, in order that they appear in the
	 *            selection. The values will be bound as Strings.
	 * @param groupBy
	 *            A filter declaring how to group rows, formatted as an SQL
	 *            GROUP BY clause (excluding the GROUP BY itself). Passing null
	 *            will cause the rows to not be grouped.
	 * @param having
	 *            A filter declare which row groups to include in the cursor, if
	 *            row grouping is being used, formatted as an SQL HAVING clause
	 *            (excluding the HAVING itself). Passing null will cause all row
	 *            groups to be included, and is required when row grouping is
	 *            not being used.
	 * @param orderBy
	 *            How to order the rows, formatted as an SQL ORDER BY clause
	 *            (excluding the ORDER BY itself). Passing null will use the
	 *            default sort order, which may be unordered.
	 * @return A list of logs.
	 */
	public List<Log> getLog(String selection, String[] args, String groupBy,
			String having, String orderBy) {
		String[] columns = Log.COLUMNS;
		Cursor c = mDatabase.query(Log.class.getSimpleName(), columns,
				selection, args, groupBy, having, orderBy);

		// Returns null if cursor is null.
		if (c == null) {
			return null;
		}

		List<Log> results = new ArrayList<Log>();

		while (c.moveToNext()) {
			Log item = new Log();
			Calendar date = Calendar.getInstance();
			date.setTimeInMillis(c.getLong(1));

			item.setId(c.getInt(0));
			item.setDate(date);
			item.setWodId(c.getInt(2));
			item.setBodyWeight(c.getInt(3));
			item.setTime(c.getLong(4));
			item.setRep(c.getInt(5));
			item.setRound(c.getInt(6));
			item.setNotes(c.getString(7));
			item.setIsInjured(c.getInt(8) == 1);

			results.add(item);
		}

		return results;
	}

	/**
	 * Gets a list of the logs by their id.
	 * 
	 * @param id
	 *            The id of the logs to be retrieved.
	 * @return A list of the logs with the given id.
	 */
	public List<Log> getLogById(int id) {
		return getLog(String.format("%s = ?", Log.COLUMNS[0]),
				new String[] { Integer.toString(id) }, null, null, null);
	}

	/**
	 * Gets a list of the logs by their WOD id.
	 * 
	 * @param id
	 *            The WOD id of the logs to be retrieved.
	 * @return A list of the logs with the given WOD id.
	 */
	public List<Log> getLogByWodId(int id) {
		return getLog(String.format("%s = ?", Log.COLUMNS[2]),
				new String[] { Integer.toString(id) }, null, null, null);
	}

	/**
	 * Gets the list of WODs from the database.
	 * 
	 * @param selection
	 *            A filter declaring which rows to return, formatted as an SQL
	 *            WHERE clause (excluding the WHERE itself). Passing null will
	 *            return all rows for the given table.
	 * @param args
	 *            You may include ?s in selection, which will be replaced by the
	 *            values from selectionArgs, in order that they appear in the
	 *            selection. The values will be bound as Strings.
	 * @param groupBy
	 *            A filter declaring how to group rows, formatted as an SQL
	 *            GROUP BY clause (excluding the GROUP BY itself). Passing null
	 *            will cause the rows to not be grouped.
	 * @param having
	 *            A filter declare which row groups to include in the cursor, if
	 *            row grouping is being used, formatted as an SQL HAVING clause
	 *            (excluding the HAVING itself). Passing null will cause all row
	 *            groups to be included, and is required when row grouping is
	 *            not being used.
	 * @param orderBy
	 *            How to order the rows, formatted as an SQL ORDER BY clause
	 *            (excluding the ORDER BY itself). Passing null will use the
	 *            default sort order, which may be unordered.
	 * @return A list of WODs.
	 */
	public List<Wod> getWod(String selection, String[] args, String groupBy,
			String having, String orderBy) {
		String[] columns = Wod.COLUMNS;
		Cursor c = mDatabase.query(Wod.class.getSimpleName(), columns,
				selection, args, groupBy, having, orderBy);

		// Returns null if cursor is null.
		if (c == null) {
			return null;
		}

		List<Wod> results = new ArrayList<Wod>();

		while (c.moveToNext()) {
			Wod item = new Wod();

			item.setId(c.getInt(0));
			item.setType(Wod.Type.values()[c.getInt(1)]);
			item.setName(c.getString(2));
			item.setDescription(c.getString(3));
			item.setRating(c.getInt(4));
			item.setPRType(Wod.PRType.values()[c.getInt(5)]);

			results.add(item);
		}

		return results;
	}

	/**
	 * Gets a list of the WODs by their id.
	 * 
	 * @param id
	 *            The id of the WODs to be retrieved.
	 * @return A list of the WODs with the given id.
	 */
	public List<Wod> getWodById(int id) {
		return getWod(String.format("%s = ?", Wod.COLUMNS[0]),
				new String[] { Integer.toString(id) }, null, null, null);
	}

	/**
	 * Gets a list of the WODs by their WOD type.
	 * 
	 * @param type
	 *            The type of the WODs to be retrieved.
	 * @return A list of the WODs with the given WOD type.
	 */
	public List<Wod> getWodByType(Wod.Type type) {
		return getWod(String.format("%s = ?", Wod.COLUMNS[1]),
				new String[] { Integer.toString(type.ordinal()) }, null, null,
				null);
	}

	/**
	 * Inserts a new entry of lift.
	 * 
	 * @param record
	 *            The lift to be inserted.
	 * @return The id of the new lift.
	 */
	public long insertLift(Lift record) {
		String[] columns = Lift.COLUMNS;

		ContentValues values = new ContentValues();
		values.put(columns[1], record.getLiftId());
		values.put(columns[2], record.getDateOne().getTime());
		values.put(columns[3], record.getDateThree().getTime());
		values.put(columns[4], record.getDateFive().getTime());
		values.put(columns[5], record.getRepOne());
		values.put(columns[6], record.getRepThree());
		values.put(columns[7], record.getRepFive());

		return mDatabase.insert(Lift.class.getSimpleName(), null, values);
	}

	/**
	 * Inserts a new entry of log.
	 * 
	 * @param record
	 *            The log to be inserted.
	 * @return The id of the new log.
	 */
	public long insertLog(Log record) {
		String[] columns = Log.COLUMNS;

		ContentValues values = new ContentValues();
		values.put(columns[1], record.getDate().getTimeInMillis());
		values.put(columns[2], record.getWodId());
		values.put(columns[3], record.getBodyWeight());
		values.put(columns[4], record.getTime());
		values.put(columns[5], record.getRep());
		values.put(columns[6], record.getRound());
		values.put(columns[7], record.getNotes());
		values.put(columns[8], record.getIsInjured());

		return mDatabase.insert(Log.class.getSimpleName(), null, values);
	}

	/**
	 * Inserts a new entry of WOD.
	 * 
	 * @param record
	 *            The WOD to be inserted.
	 * @return The id of the new WOD.
	 */
	public long insertWod(Wod record) {
		String[] columns = Wod.COLUMNS;

		ContentValues values = new ContentValues();
		values.put(columns[1], record.getType().ordinal());
		values.put(columns[2], record.getName());
		values.put(columns[3], record.getDescription());
		values.put(columns[4], record.getRating());
		values.put(columns[5], record.getPRType().ordinal());

		return mDatabase.insert(Wod.class.getSimpleName(), null, values);
	}

	/**
	 * @see android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite.SQLiteDatabase)
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		mDatabase = db;
		String[] columns;

		columns = Lift.COLUMNS;
		db.execSQL(String.format("CREATE TABLE %s ("
				+ "%s INTEGER PRIMARY KEY AUTOINCREMENT," // Id
				+ "%s INTEGER, " // LiftId
				+ "%s LONG, " // DateOne
				+ "%s LONG, " // DateThree
				+ "%s LONG, " // DateFive
				+ "%s INTEGER, " // RepOne
				+ "%s INTEGER, " // RepThree
				+ "%s INTEGER);" // RepFive
		, Lift.class.getSimpleName(), columns[0], columns[1], columns[2],
				columns[3], columns[4], columns[5], columns[6], columns[7]));

		columns = Log.COLUMNS;
		db.execSQL(String.format("CREATE TABLE %s ("
				+ "%s INTEGER PRIMARY KEY AUTOINCREMENT," // Id
				+ "%s LONG, " // Date
				+ "%s INTEGER, " // WodId
				+ "%s INTEGER, " // BodyWeight
				+ "%s LONG, " // Time
				+ "%s INTEGER, " // Rep
				+ "%s INTEGER, " // Round
				+ "%s INTEGER, " // Notes
				+ "%s INTEGER);" // IsInjured
		, Log.class.getSimpleName(), columns[0], columns[1], columns[2],
				columns[3], columns[4], columns[5], columns[6], columns[7],
				columns[8]));

		columns = Wod.COLUMNS;
		db.execSQL(String.format("CREATE TABLE %s ("
				+ "%s INTEGER PRIMARY KEY AUTOINCREMENT," // Id
				+ "%s INTEGER, " // Type
				+ "%s TEXT, " // Name
				+ "%s TEXT, " // Description
				+ "%s INTEGER, " // Rating
				+ "%s INTEGER);" // PR Type
		, Wod.class.getSimpleName(), columns[0], columns[1], columns[2],
				columns[3], columns[4], columns[5]));

		// Initializes local variables
		Resources res = mContext.getResources();
		String[] names, descriptions;
		int[] prTypes;

		// Stores girl wod resources into database.
		names = res.getStringArray(R.array.girl_benchmarks);
		descriptions = res.getStringArray(R.array.girl_descriptions);
		prTypes = res.getIntArray(R.array.girl_benchmarks_filter);

		for (int i = 0; i < names.length; i++) {
			Wod item = new Wod();
			item.setType(Wod.Type.Girl);
			item.setName(names[i]);
			item.setDescription(descriptions[i]);
			item.setPRType(Wod.PRType.values()[prTypes[i]]);

			insertWod(item);
		}

		// Stores hero wod resources into database.
		names = res.getStringArray(R.array.hero_benchmarks);
		descriptions = res.getStringArray(R.array.hero_descriptions);
		prTypes = res.getIntArray(R.array.hero_benchmarks_filter);
		for (int i = 0; i < names.length; i++) {
			Wod item = new Wod();
			item.setType(Wod.Type.Hero);
			item.setName(names[i]);
			item.setDescription(descriptions[i]);
			item.setPRType(Wod.PRType.values()[prTypes[i]]);

			insertWod(item);
		}

		// Stores other wod resources into database.
		names = res.getStringArray(R.array.other_benchmarks);
		descriptions = res.getStringArray(R.array.other_descriptions);
		prTypes = res.getIntArray(R.array.other_benchmarks_filter);
		for (int i = 0; i < names.length; i++) {
			Wod item = new Wod();
			item.setType(Wod.Type.Other);
			item.setName(names[i]);
			item.setDescription(descriptions[i]);
			item.setPRType(Wod.PRType.values()[prTypes[i]]);

			insertWod(item);
		}

		// Set up the Lift table with empty data.
		int nLifts = res.getStringArray(R.array.lifts).length;

		for (int i = 0; i < nLifts; i++) {
			Lift item = new Lift();
			item.setLiftId(i);
			insertLift(item);
		}
	}

	/**
	 * @see android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite.SQLiteDatabase,
	 *      int, int)
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		if (oldVersion != newVersion) {
			db.execSQL(String.format("DROP TABLE IF EXISTS %s",
					Lift.class.getSimpleName()));
			db.execSQL(String.format("DROP TABLE IF EXISTS %s",
					Log.class.getSimpleName()));
			db.execSQL(String.format("DROP TABLE IF EXISTS %s",
					Wod.class.getSimpleName()));
			onCreate(db);
		}
	}

	/**
	 * Updates the lift by its id.
	 * 
	 * @param id
	 *            Id of the lift.
	 * @param record
	 *            The new lift.
	 * @return The number of rows affected.
	 */
	public int updateLift(Lift record) {
		String[] columns = Lift.COLUMNS;

		ContentValues values = new ContentValues();
		values.put(columns[0], record.getId());
		values.put(columns[1], record.getDateOne().getTime());
		values.put(columns[2], record.getDateThree().getTime());
		values.put(columns[3], record.getDateFive().getTime());
		values.put(columns[4], record.getRepOne());
		values.put(columns[5], record.getRepThree());
		values.put(columns[6], record.getRepFive());

		return mDatabase.update(Lift.class.getSimpleName(), values,
				String.format("%s = ?", columns[0]),
				new String[] { Integer.toString(record.getId()) });
	}

	/**
	 * Updates the log by its id.
	 * 
	 * @param id
	 *            Id of the log.
	 * @param record
	 *            The new log.
	 * @return The number of rows affected.
	 */
	public int updateLog(Log record) {
		String[] columns = Log.COLUMNS;

		ContentValues values = new ContentValues();
		values.put(columns[0], record.getId());
		values.put(columns[1], record.getDate().getTimeInMillis());
		values.put(columns[2], record.getWodId());
		values.put(columns[3], record.getBodyWeight());
		values.put(columns[4], record.getTime());
		values.put(columns[5], record.getRep());
		values.put(columns[6], record.getRound());
		values.put(columns[7], record.getNotes());
		values.put(columns[8], record.getIsInjured());

		return mDatabase.update(Log.class.getSimpleName(), values,
				String.format("%s = ?", columns[0]),
				new String[] { Integer.toString(record.getId()) });
	}

	/**
	 * Updates the WOD by its id.
	 * 
	 * @param id
	 *            Id of the WOD.
	 * @param record
	 *            The new WOD.
	 * @return The number of rows affected.
	 */
	public int updateWod(Wod record) {
		String[] columns = Wod.COLUMNS;

		ContentValues values = new ContentValues();
		values.put(columns[0], record.getId());
		values.put(columns[1], record.getType().ordinal());
		values.put(columns[2], record.getName());
		values.put(columns[3], record.getDescription());
		values.put(columns[4], record.getRating());

		return mDatabase.update(Wod.class.getSimpleName(), values,
				String.format("%s = ?", columns[0]),
				new String[] { Integer.toString(record.getId()) });
	}
}